package com.enfi.api.modules.bz.controller;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.enfi.api.modules.bz.domain.ComparingQuery;
import com.enfi.api.modules.bz.service.AutomaticDataService;
import com.enfi.api.util.DateTimeUtils;
import com.enfi.api.util.DateUtils;
import com.enfi.api.util.SecUser;
import com.enfi.api.util.UserUtils;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.SneakyThrows;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author wind
 * @date 2020/3/12
 */
@Controller
@RequestMapping(value = "matic")
public class AutomaticSystemController {
    @Autowired
    HttpServletRequest request;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private AutomaticDataService automaticDataService;

    @DS("secondary")
    private List secondaryQueryForList(String sql,String... strings){
        return  jdbcTemplate.queryForList(sql,strings);
    }
    @DS("secondary")
    private Map secondaryQueryForMap(String sql,String... strings){
        return  jdbcTemplate.queryForMap(sql,strings);
    }

    @DS("yxData")
    private List yxDataQueryForList(String sql,String... strings){
       return jdbcTemplate.queryForList(sql,strings);
    }
    /**
     * 获取工厂列表
     * select office_code as officeCode,office_name as officeName from js_sys_office where office_type =2  and status = 0
     * and EXISTS (
     * select 1 from js_sys_user_data_scope where  ctrl_type="waterFactory"  and user_code="wangfeixiao_w95g" and ctrl_data=office_code
     * )
     * union
     * select office_code as officeCode,office_name as officeName from js_sys_office where office_type =2 and status = 0
     * and EXISTS (
     * select 1 from js_sys_role_data_scope where  ctrl_type="waterFactory"  and role_code in ("hd_role_004") and ctrl_data=office_code
     * )
     *
     * @param officeType 机构类型 2获取子公司 4获取工厂
     * @param parentCode 父节点编码 获取工厂时必传
     * @return
     */
    @RequestMapping(value = {"getFactoryList"})
    @ResponseBody
    @SneakyThrows
    public List<Map<String, Object>> getFactoryList(@RequestParam(value = "officeType", required = true) List<String> officeType, String parentCode, String officeCode, Boolean ignore) {
        SecUser secUser = UserUtils.getSecUser();
        NamedParameterJdbcTemplate givenParamJdbcTemp = new NamedParameterJdbcTemplate(jdbcTemplate);
        Map<String, Object> args = Maps.newHashMap();
        List<String> roleIds=secUser.getAuthoritiesToList();
        String userCode=secUser.getId();
        if (ignore != null && ignore) {
            roleIds=Lists.newArrayList("CJGLY");
            userCode="admin";
        }
        args.put("userCode", userCode);
        //args.put("officeType",officeType);
        if (StringUtils.isNotBlank(parentCode)) {
            args.put("parentCode", parentCode);
        }
        if (StringUtils.isNotBlank(officeCode)) {
            args.put("officeCode", officeCode);
        }
        if (CollectionUtils.isEmpty(officeType)) {
            args.put("officeType", Lists.newArrayList(""));
        } else {
            args.put("officeType", officeType);
        }
        args.put("roleIds", roleIds);


        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        String sql = "select office_code as officeCode,office_name as officeName,office_type as officeType from js_sys_office where office_type in (:officeType) and status = 0 ";
        if (StringUtils.isNotBlank(parentCode)) {
            sql += " and parent_code=:parentCode";
        }
        if (StringUtils.isNotBlank(officeCode)) {
            sql += " and office_code=:officeCode";
        }
        sql += " and EXISTS (\n" +
                "      select 1 from js_sys_user_data_scope where  ctrl_type='waterFactory'  and user_code=:userCode and ctrl_data=office_code " +
                "      ) ";
        sql += " union ";
        sql += "select office_code as officeCode,office_name as officeName,office_type as officeType from js_sys_office where office_type in (:officeType) and status = 0 ";
        if (StringUtils.isNotBlank(parentCode)) {
            sql += " and parent_code=:parentCode";
        }
        if (StringUtils.isNotBlank(officeCode)) {
            sql += " and office_code=:officeCode";
        }
        sql += "  and EXISTS (\n" +
                "      select 1 from js_sys_role_data_scope where  ctrl_type='waterFactory'  and role_code in (:roleIds) and ctrl_data=office_code " +
                "      )";

        givenParamJdbcTemp.query(sql, args, (rs, index) -> {
            Map map = Maps.newHashMap();
            map.put("officeCode", rs.getString(1));
            map.put("officeName", rs.getString(2));
            map.put("officeType", rs.getString(3));
            listMap.add(map);
            return null;
        });
        return listMap;
    }

    /**
     * 采集项查询接口
     *
     * @param factoryCode   工厂编码
     * @param indexType     类型 1查询实时数据 2查询填报数据 3营销
     * @param statIndexCode 分项编码
     * @return
     */
    //指标查询接口
    @RequestMapping(value = {"listIndexNew"})
    @ResponseBody
    public List<Map<String, Object>> listIndexNew(String factoryCode, Integer indexType, String statIndexCode) {
        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        //indexType=1时查询自控系统参数
        if (indexType != null && indexType == 1) {
            if (StringUtils.isBlank(statIndexCode)) {
                //只查询统计指标
                String sql = "select monitor_code as statIndexCode,monitor_name as statIndexName,position_order,monitor_order from automatic_system_menu_all" +
                        " where factory_code = '" + factoryCode + "'" +
                        " group by monitor_code,monitor_name,position_order,monitor_order" +
                        " order by position_order,monitor_order";
                listMap = jdbcTemplate.queryForList(sql);
            }
            if (StringUtils.isNotBlank(statIndexCode)) {
                //只查询可选择指标
                String sql = "select sensor_code as statIndexCode,sensor_name as statIndexName,tag_stage as tagStage,sersor_order from automatic_system_menu_all" +
                        " where factory_code = '" + factoryCode + "' and monitor_code = '" + statIndexCode + "'" +
                        " order by sersor_order";
                listMap = jdbcTemplate.queryForList(sql);
            }
        }
        //indexType=2时查询工艺运行指标
        if (indexType != null && indexType == 2) {
            if (StringUtils.isBlank(statIndexCode)) {
                //只查询统计指标
                String sql = "select name as statIndexCode,name as statIndexName,`order` from process_operation_menu_all" +
                        " where factory_code = '" + factoryCode + "' and name <> ''" +
                        " group by name,`order` order by `order`";
                listMap = jdbcTemplate.queryForList(sql);
            }
            if (StringUtils.isNotBlank(statIndexCode)) {
                //只查询可选择指标
                String sql = "select code as statIndexCode,fz as statIndexName,code as tagStage from process_operation_menu_all" +
                        " where factory_code = '" + factoryCode + "' and name = '" + statIndexCode + "'" +
                        " order by `order`";
                listMap = jdbcTemplate.queryForList(sql);
            }
        }
        //indexType=3时查询营销指标
        if (indexType != null && indexType == 3) {
            if (StringUtils.isBlank(statIndexCode)) {
                //只查询统计指标
                String sql = "select distinct zbfl as statIndexCode,zbfl as statIndexName  from ef_zb where ssbm1='" + factoryCode + "' order by zbfl";
                listMap =yxDataQueryForList(sql);
            }
            if (StringUtils.isNotBlank(statIndexCode)) {
                //只查询可选择指标
                String sql = "select zbm as statIndexName,CONCAT(zbbm,'|',ssbm) as statIndexCode,CONCAT(zbbm,'|',ssbm) as tagStage from ef_zb where ssbm1='" + factoryCode + "' and zbfl='" + statIndexCode + "' order by zbm";
                listMap = yxDataQueryForList(sql);
            }
        }
        return listMap;
    }


    //指标查询接口
    @RequestMapping(value = {"listIndex"})
    @ResponseBody
    public List<Map<String, Object>> listIndex(String factoryCode, Integer indexType, String statIndexCode) {
        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        //indexType=1时查询自控系统参数
        if (indexType != null && indexType == 1) {
            if (StringUtils.isBlank(statIndexCode)) {
                //只查询统计指标
                String sql = "select monitor_code as statIndexCode,monitor_name as statIndexName from automatic_system_menu where factory = '" + factoryCode + "' group by monitor_code,monitor_name";
                listMap = secondaryQueryForList(sql);
            }
            if (StringUtils.isNotBlank(statIndexCode)) {
                //只查询可选择指标
                String sql = "select sensor_code as statIndexCode,sensor_name as statIndexName,tag_stage as tagStage from automatic_system_menu where factory = '" + factoryCode + "' and monitor_code = '" + statIndexCode + "'";
                listMap = secondaryQueryForList(sql);
            }
        }
        //indexType=3时查询工艺运行指标
        if (indexType != null && indexType == 3) {
            if (StringUtils.isBlank(statIndexCode)) {
                //只查询统计指标
                String sql = "select fz as statIndexCode,fz as statIndexName from process_operation_menu where factory = '" + factoryCode + "' and fz <> '' group by fz";
                listMap = secondaryQueryForList(sql);
            }
            if (StringUtils.isNotBlank(statIndexCode)) {
                //只查询可选择指标
                String sql = "select code as statIndexCode,name as statIndexName,code as tagStage from process_operation_menu where factory = '" + factoryCode + "' and fz = '" + statIndexCode + "'";
                listMap = secondaryQueryForList(sql);
            }
        }
        return listMap;
    }

    //图表查询
    //http://localhost:8096/matic/chart/search?tagStages=LANZHOUIBPD17&startTime=2019-01-24&endTime=2019-01-30&indexType=2
    @RequestMapping(value = {"/chart/search"})
    @ResponseBody
    public Map<String, Object> search(Integer indexType, String startTime, String endTime, @RequestParam(defaultValue = "1") Integer timeInterval, @RequestParam(defaultValue = "5") Integer timeType, @RequestParam List<String> tagStages, @RequestParam List<String> statIndexNames) throws Exception {

        Map<String, Object> map = new HashMap<String, Object>();
        //indexType=1时查询自控系统参数
        if (indexType != null && indexType == 1) {
            return getHbase(indexType, startTime, endTime, timeInterval, timeType, tagStages, statIndexNames);
        }
        //indexType=2时查询工艺运行指标
        if (indexType != null && indexType == 2) {
            List<String> times = DateUtils.findDates(startTime, endTime);
            map.put("time", times);
            List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
            for (int k = 0; k < tagStages.size(); k++) {
                Map<String, Object> statIndexNameMap = new HashMap<String, Object>();
                statIndexNameMap.put("statIndexName", statIndexNames.get(k));
                List<Object> values = new ArrayList<Object>();
                String sql2 = "select dt,if(`value`= '',0,`value`) as value from interval_analysis where code = ? and dt >= ? and dt <= ? order by id";
                List<Map<String, Object>> valueList = secondaryQueryForList(sql2, tagStages.get(k), startTime.replace("-", ""), endTime.replace("-", ""));
                int j = 0;
                for (int i = 0; i < times.size(); i++) {
                    if (valueList.size() > j && valueList.get(j).get("dt").toString().equals(times.get(i).replace("-", ""))) {
                        values.add(valueList.get(j).get("value"));
                        j++;
                    } else {
                        values.add(0);
                    }
                }
                statIndexNameMap.put("values", values);
                listMap.add(statIndexNameMap);
            }
            map.put("list", listMap);
        }
        //indexType=3时查询营销指标
        if (indexType != null && indexType == 3) {
            List<String> months = DateUtils.findMonths(startTime, endTime);
            map.put("time", months);
            List<String[]> tags = Lists.newArrayList();
            for (String tag : tagStages) {
                tags.add(tag.split("\\|"));
            }
            List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
            for (int i = 0; i < tags.size(); i++) {
                Map<String, Object> statIndexNameMap = Maps.newHashMap();
                statIndexNameMap.put("statIndexName", statIndexNames.get(i));
                StringBuilder sql = new StringBuilder();
                sql.append("select yue,").append(tags.get(i)[0]).append(" as value from ef_yue where yue>=? and yue <=? and ssbm=").append(tags.get(i)[1]).append(" order by yue");
                List<Map<String, Object>> yxList =yxDataQueryForList(sql.toString(), startTime.replace("-", "").substring(0, 6),
                        endTime.replace("-", "").substring(0, 6));
                List<Object> values = Lists.newArrayList();
                int j = 0;
                for (int ii = 0; ii < months.size(); ii++) {
                    if (yxList.size() > j && yxList.get(j).get("yue").toString().equals(months.get(ii).replace("-", ""))) {
                        values.add(yxList.get(j).get("value"));
                        j++;
                    } else {
                        values.add(0);
                    }
                }
                statIndexNameMap.put("values", values);
                listMap.add(statIndexNameMap);
            }
            map.put("list", listMap);
        }
        return map;
    }

    private Map getHbase(Integer indexType, String startTime, String endTime, Integer timeIntegerval, Integer timeType, List<String> tagStages, List<String> statIndexNames) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        Map map1 = automaticDataService.getCharts(tagStages, generateTimeSequ(startTime, endTime, timeIntegerval, timeType));
        List listMap = Lists.newArrayList();
        for (int i = 0; i < tagStages.size(); i++) {
            if (map.get("time") == null) {
                Set<String> set = ((Map) map1.get(tagStages.get(i))).keySet();
                if (timeType == 5) {
                    //201907191201
                    map.put("time", set.stream().map(t -> t.substring(0, 4) + "-" + t.substring(4, 6) + "-" + t.substring(6, 8)).collect(Collectors.toList()));
                } else if (timeType == 10) {
                    map.put("time", set.stream().map(t -> t.substring(0, 4) + "-" + t.substring(4, 6) + "-" + t.substring(6, 8) + " " + t.substring(8, 10)).collect(Collectors.toList()));
                } else if (timeType == 12) {
                    map.put("time", set.stream().map(t -> t.substring(0, 4) + "-" + t.substring(4, 6) + "-" + t.substring(6, 8) + ":" + t.substring(8, 10) + ":" + t.substring(10, 12)).collect(Collectors.toList()));
                } else {
                    map.put("time", set);
                }
            }
            Map map2 = Maps.newHashMap();
            map2.put("statIndexName", statIndexNames.get(i));
            map2.put("values", ((Map) map1.get(tagStages.get(i))).values());
            listMap.add(map2);
        }
        map.put("list", listMap);
        return map;
    }

    private static SimpleDateFormat rowkeyDateFormat = new SimpleDateFormat("yyyyMMddHHmm");

    @SneakyThrows
    private static List<String> generateTimeSequ(String startTime, String endTime, int interval, int interval_unit){
        Calendar startCalendar = Calendar.getInstance();
        Calendar endCalendar = Calendar.getInstance();
        startCalendar.setTime(DateTimeUtils.convert(startTime));
        startCalendar.add(Calendar.MINUTE, 1);
        endCalendar.setTime(DateTimeUtils.convert(endTime));
        endCalendar.add(Calendar.MINUTE, 1);
        //为避免时间比较出现偏差，给结束时间加一秒，不影响结果
        endCalendar.add(Calendar.SECOND, 1);
        //存放生成的时间序列
        List<String> timeSequence = Lists.newArrayList();
        while (true) {
            String rowkeyTime = rowkeyDateFormat.format(startCalendar.getTime());
            timeSequence.add(rowkeyTime);
            startCalendar.add(interval_unit, interval);
            int result = startCalendar.compareTo(endCalendar);
            if (result >= 0) {
                break;
            }
        }
        return timeSequence;
    }

    //对比分析
    //indexType 1或3
    //测试地址  http://localhost:8096/matic/contrast/search?tagStage=LANZHOUIBPD126&indexType=3&comparingTime=2017-01-01,2017-02-02
    @RequestMapping(value = {"/contrast/search"})
    @ResponseBody
    public Map<String, Object> contrastSearch(@RequestParam(defaultValue = "1") Integer indexType, Integer timeIntegerval, Integer timeType, String tagStage, ComparingQuery comparingQuery, String statIndexName) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        //indexType=1时查询自控系统参数
        if (indexType != null && indexType == 1) {
            List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
            comparingQuery.getComparingTime().forEach(v -> {
                Map map1 = Maps.newHashMap();
                String startTime = v.get("startTime").toString();
                String endTime = v.get("endTime").toString();

                try {
                    Map map2 = getHbase(indexType, startTime, endTime, 1, 5, Lists.newArrayList(tagStage), Lists.newArrayList(statIndexName));
                    map1.put("values", ((Map) ((List) map2.get("list")).get(0)).get("values"));
                    map1.put("time", map2.get("time"));
                    if (map.get("statIndexName") == null) {
                        map.put("statIndexName", ((Map) ((List) map2.get("list")).get(0)).get("statIndexName"));
                    }
                    listMap.add(map1);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            });
            map.put("list", listMap);
        }
        //indexType=3时查询工艺运行指标
        if (indexType != null && indexType == 2) {
            map.put("statIndexName", statIndexName);
            List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
            for (int i = 0; i < comparingQuery.getComparingTime().size(); i++) {
                Map<String, Object> map1 = new HashMap<String, Object>();
                String startTime = comparingQuery.getComparingTime().get(i).get("startTime").toString();
                String endTime = comparingQuery.getComparingTime().get(i).get("endTime").toString();
                List<String> times = DateUtils.findDates(startTime, endTime);
                map1.put("time", times);
                List<Object> values = new ArrayList<Object>();
                String sql2 = "select dt,if(`value`= '',0,`value`) as value from interval_analysis where code = ? and dt >= ? and dt <= ? order by id";
                List<Map<String, Object>> valueList = secondaryQueryForList(sql2, tagStage, startTime.replace("-", ""), endTime.replace("-", ""));
                int j = 0;
                for (int k = 0; k < times.size(); k++) {
                    if (valueList.size() > j && valueList.get(j).get("dt").toString().equals(times.get(k).replace("-", ""))) {
                        values.add(valueList.get(j).get("value"));
                        j++;
                    } else {
                        values.add(0);
                    }
                }
                map1.put("values", values);
                listMap.add(map1);
            }
            map.put("list", listMap);
        }
        //indexType=3 营销指标查询
        if (indexType != null && indexType == 3) {
            map.put("statIndexName", statIndexName);
            List<Map<String, Object>> listMap = Lists.newArrayList();
            for (int i = 0; i < comparingQuery.getComparingTime().size(); i++) {
                Map<String, Object> yxMap = Maps.newHashMap();
                String startTime = comparingQuery.getComparingTime().get(i).get("startTime").toString();
                String endTime = comparingQuery.getComparingTime().get(i).get("endTime").toString();
                List<String> months = DateUtils.findMonths(startTime, endTime);
                yxMap.put("time", months);
                StringBuilder sql = new StringBuilder();
                sql.append("select yue,").append(tagStage.split("\\|")[0]).append(" as value from ef_yue where yue>=? and yue <=? and ssbm=").append(tagStage.split("\\|")[1]).append(" order by yue");
                List<Map<String, Object>> yxList = yxDataQueryForList(sql.toString(), startTime.replace("-", "").substring(0, 6), endTime.replace("-", "").substring(0, 6));
                List<Object> values = Lists.newArrayList();
                int j = 0;
                for (int ii = 0; ii < months.size(); ii++) {
                    if (yxList.size() > j && yxList.get(j).get("yue").toString().equals(months.get(ii).replace("-", ""))) {
                        values.add(yxList.get(j).get("value"));
                        j++;
                    } else {
                        values.add(0);
                    }
                }
                yxMap.put("values", values);
                listMap.add(yxMap);
            }
            map.put("list", listMap);
        }
        return map;
    }

    //相关性分析
    //indexType 1或3
    //测试地址  http://localhost:8096/matic/relevance/search?xAxis=LANZHOUIBPD126&yAxis=LANZHOUIBPD10&indexType=3&startTime=2015-05-25&endTime=2015-05-30
    @RequestMapping(value = {"/relevance/search"})
    @ResponseBody
    @DS("")
    public Map<String, Object> relevanceSearch(@RequestParam(defaultValue = "1") Integer indexType, String startTime, String endTime, Integer timeIntegerval, Integer timeType, String xAxis, String yAxis, String xStatIndexName, String yStatIndexName) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        //indexType=1时查询自控系统参数
        if (indexType != null && indexType == 1) {
            List tagStages = Lists.newArrayList(xAxis, yAxis);
            List tagStageNames = Lists.newArrayList(xStatIndexName, yStatIndexName);
            Map map2 = getHbase(indexType, startTime, endTime, 1, 5, tagStages, tagStageNames);
            map.put("time", map2.get("time"));
            map.put("list", map2.get("list"));
        }
        //indexType=3时查询工艺运行指标
        if (indexType != null && indexType == 2) {
            List<String> times = DateUtils.findDates(startTime, endTime);
            map.put("time", times);
            List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
            Map<String, Object> statIndexNameMap = Maps.newHashMap();
            List<Object> values = new ArrayList<Object>();
            String sql2 = "select dt,if(`value`= '',0,`value`) as value from interval_analysis where code = ? and dt >= ? and dt <= ? order by id";
            List<Map<String, Object>> valueList = secondaryQueryForList(sql2, xAxis, startTime.replace("-", ""), endTime.replace("-", ""));
            int j = 0;
            for (int k = 0; k < times.size(); k++) {
                if (valueList.size() > j && valueList.get(j).get("dt").toString().equals(times.get(k).replace("-", ""))) {
                    values.add(valueList.get(j).get("value"));
                    j++;
                } else {
                    values.add(0);
                }
            }
            statIndexNameMap.put("statIndexName", xStatIndexName);
            statIndexNameMap.put("values", values);
            listMap.add(statIndexNameMap);

            statIndexNameMap = Maps.newHashMap();
            values = new ArrayList<Object>();
            valueList = secondaryQueryForList(sql2, yAxis, startTime.replace("-", ""), endTime.replace("-", ""));
            j = 0;
            for (int k = 0; k < times.size(); k++) {
                if (valueList.size() > j && valueList.get(j).get("dt").toString().equals(times.get(k).replace("-", ""))) {
                    values.add(valueList.get(j).get("value"));
                    j++;
                } else {
                    values.add(0);
                }
            }
            statIndexNameMap.put("statIndexName", yStatIndexName);
            statIndexNameMap.put("values", values);
            listMap.add(statIndexNameMap);
            map.put("list", listMap);
        }
        //indexType=3 营销指标数据查询
        if (indexType != null && indexType == 3) {
            Map<String, Object> yxMap = Maps.newHashMap();//营销数据map
            List<String> months = DateUtils.findMonths(startTime, endTime);
            map.put("time", months);
            StringBuilder sql = new StringBuilder();
            List<Map<String, Object>> listMap = Lists.newArrayList();//xy数据map集合
            List<Object> values = Lists.newArrayList();//数据按照month对应补充
            //x轴
            sql.append("select yue,").append(xAxis.split("\\|")[0]).append(" as value from ef_yue where yue>=? and yue <=? and ssbm=").append(xAxis.split("\\|")[1]).append(" order by yue");
            List<Map<String, Object>> xAxisList = yxDataQueryForList(sql.toString(), startTime.replace("-", "").substring(0, 6), endTime.replace("-", "").substring(0, 6));
            int j = 0;
            for (int ii = 0; ii < months.size(); ii++) {
                if (xAxisList.size() > j && xAxisList.get(j).get("yue").toString().equals(months.get(ii).replace("-", ""))) {
                    values.add(xAxisList.get(j).get("value"));
                    j++;
                } else {
                    values.add(0);
                }
            }
            yxMap.put("statIndexName", xStatIndexName);
            yxMap.put("values", values);
            listMap.add(yxMap);

            //y轴
            yxMap = Maps.newHashMap();//初始化yxmap
            values = Lists.newArrayList();//初始化values集合
            sql = new StringBuilder();//初始化sql
            sql.append("select yue,").append(yAxis.split("\\|")[0]).append(" as value from ef_yue where yue>=? and yue <=? and ssbm=").append(yAxis.split("\\|")[0]).append(" order by yue");
            List<Map<String, Object>> yAxisList =yxDataQueryForList(sql.toString(), startTime.replace("-", "").substring(0, 6), endTime.replace("-", "").substring(0, 6));
            j = 0;
            for (int ii = 0; ii < months.size(); ii++) {
                if (yAxisList.size() > j && yAxisList.get(j).get("yue").toString().equals(months.get(ii).replace("-", ""))) {
                    values.add(yAxisList.get(j).get("value"));
                    j++;
                } else {
                    values.add(0);
                }
            }
            yxMap.put("statIndexName", yStatIndexName);
            yxMap.put("values", values);
            listMap.add(yxMap);
            map.put("list", listMap);
        }
        return map;
    }

    //区间分析
    //测试地址  http://localhost:8096/matic/interval/search?factory=兰州污水厂&startTime=2015-05-25&endTime=2015-05-25
    @RequestMapping(value = {"/interval/search"})
    @ResponseBody
    public Map<String, Object> intervalSearch(String factoryCode, String startTime, String endTime) throws ParseException {
        Map<String, Object> map = new HashMap<String, Object>();
        List<String> times = DateUtils.findDates(startTime, endTime);
        map.put("time", times);

        //查询全部指标名称、单位、上下限 按排序
        String sql1 = "select \n" +
                "  groupName, colName, max, min, unit,dt,round(max(if(`value` = '-10000000',0.0,cast(`value` as DECIMAL(20,6)))),2) as `value`,`code` as id,sort,`order`\n" +
                " from \n" +
                "(\n" +
                "  SELECT tmp1.groupName, tmp1.colName, tmp1.max, tmp1.min, tmp1.unit,'' as `value`,tmp1.`code`,tmp1.`order`,tmp1.sort\n" +
                "    , tmp2.`dt`\n" +
                "  FROM \n" +
                "  (\n" +
                "    SELECT tmp1.fz AS groupName, tmp1.`code` , tmp1.`name` AS colName, tmp1.unit, tmp1.low AS min,tmp1.`order`,tmp2.sort\n" +
                "      , tmp1.high AS max\n" +
                "    FROM \n" +
                "    (\n" +
                "      SELECT *\n" +
                "      FROM process_operation_menu_all\n" +
                "      WHERE (factory_code = '" + factoryCode + "'\n" +
                "        AND fz <> ''\n" +
                "        AND STATUS = 1\n" +
                "        AND is_in_report = 1)\n" +
                "    ) tmp1\n" +
                "    LEFT JOIN \n" +
                "    (\n" +
                "      SELECT fz, MIN(`order`) AS sort\n" +
                "      FROM process_operation_menu_all\n" +
                "      WHERE (factory_code = '" + factoryCode + "'\n" +
                "        AND fz <> ''\n" +
                "        AND STATUS = 1\n" +
                "        AND is_in_report = 1)\n" +
                "      GROUP BY fz\n" +
                "    ) tmp2\n" +
                "      ON tmp1.fz = tmp2.fz\n" +
                "  ) tmp1,\n" +
                "  (\n" +
                "    SELECT DATE_ID as dt\n" +
                "    FROM date_day\n" +
                "    WHERE DATE_ID >= '" + startTime.replace("-", "") + "' AND DATE_ID <= '" + endTime.replace("-", "") + "'\n" +
                "  ) tmp2\n" +
                "  union all\n" +
                "  SELECT tmp1.groupName, tmp1.colName, tmp1.max, tmp1.min, tmp1.unit, tmp2.`value`,tmp1.`code`,tmp1.`order`,tmp1.sort,tmp2.dt\n" +
                "  FROM \n" +
                "  (\n" +
                "    SELECT tmp1.fz AS groupName, tmp1.`code` AS code, tmp1.`name` AS colName, tmp1.unit, tmp1.low AS min,tmp1.`order`,tmp2.sort\n" +
                "      , tmp1.high AS max\n" +
                "    FROM \n" +
                "    (\n" +
                "      SELECT *\n" +
                "      FROM process_operation_menu_all\n" +
                "      WHERE (factory_code = '" + factoryCode + "'\n" +
                "        AND fz <> ''\n" +
                "        AND STATUS = 1\n" +
                "        AND is_in_report = 1)\n" +
                "    ) tmp1\n" +
                "    LEFT JOIN \n" +
                "    (\n" +
                "      SELECT fz, MIN(`order`) AS sort\n" +
                "      FROM process_operation_menu_all\n" +
                "      WHERE (factory_code = '" + factoryCode + "'\n" +
                "        AND fz <> ''\n" +
                "        AND STATUS = 1\n" +
                "        AND is_in_report = 1)\n" +
                "      GROUP BY fz\n" +
                "    ) tmp2\n" +
                "    ON tmp1.fz = tmp2.fz\n" +
                "  ) tmp1\n" +
                "  JOIN (\n" +
                "    SELECT `code`,`value`,dt\n" +
                "    FROM interval_analysis\n" +
                "    WHERE dt >= '" + startTime.replace("-", "") + "'\n" +
                "     AND dt <= '" + endTime.replace("-", "") + "'\n" +
                "  ) tmp2\n" +
                "  ON tmp1.code = tmp2.`code`\n" +
                ") as tmp group by groupName, colName, max, min, unit,dt,`code`, sort,`order`\n" +
                "    ORDER BY sort,`order`";
        List<Map<String, Object>> listMap = secondaryQueryForList(sql1);
        map.put("list", listMap);
        return map;
    }

    //区间分析值区间查询
    //测试地址  http://localhost:8096/matic/valueInterval/search?id=LANZHOUIBPD63&startTime=2015-05-25&endTime=2015-05-29&valueIntervals=200,500|500,800
    //valueIntervals格式   minValue,maxValue|minValue,maxValue
    @RequestMapping(value = {"/valueInterval/search"})
    @ResponseBody
    public List<Map<String, Object>> valueIntervalSearch(String id, String startTime, String endTime, String valueIntervals) throws ParseException {
        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        //取总数量
        String sql = "select count(*) as count from interval_analysis where code = '" + id + "' and dt >= '" + startTime.replace("-", "") + "' and dt <= '" + endTime.replace("-", "") + "'";
        Map<String, Object> allCountMap = secondaryQueryForMap(sql);
        Integer allCount = Integer.parseInt(allCountMap.get("count").toString());
        String[] valueIntervalArray = valueIntervals.split("\\|");
        for (int i = 0; i < valueIntervalArray.length; i++) {
            Map<String, Object> map = new HashMap<String, Object>();
            Integer startValue = Integer.parseInt(valueIntervalArray[i].split("\\,")[0]);
            Integer endValue = Integer.parseInt(valueIntervalArray[i].split("\\,")[1]);
            String sql1 = "select count(*) as count from interval_analysis where code = '" + id + "' and value >= " + startValue + " and value <= " + endValue + " and dt >= '" + startTime.replace("-", "") + "' and dt <= '" + endTime.replace("-", "") + "'";
            Map<String, Object> countMap = secondaryQueryForMap(sql1);
            map.put("startValue", startValue);
            map.put("endValue", endValue);
            map.put("count", countMap.get("count"));
            if (StringUtils.equalsIgnoreCase(countMap.get("count").toString(), "0")) {
                map.put("percent", 0);//百分比
            } else {
                Double percent = (Double.valueOf(countMap.get("count").toString()) / allCount) * 100;
                map.put("percent", new DecimalFormat("0.00").format(percent));//百分比
            }
            listMap.add(map);
        }
        return listMap;
    }
}

